import pandas as pd
from sqlalchemy import create_engine
import os
from station.models import Station


curDir = os.path.dirname(os.path.abspath(__file__))
appDir = os.path.dirname(curDir)
baseDir = os.path.dirname(appDir)

excel_path = r'D:\E\projects\工位信息系统\django-vue3-admin20230921\backend\station\temp\station_c62x_20230613.xlsx' 

'../temp/dbsp.sqlite3'
db_path = os.path.join(appDir, 'db.sqlite3')
db_engine = create_engine(f'sqlite:///{db_path}', echo=True)

# 配置db_table名称，以及字段和excel列名之间的对应关系
table_field_col_dict = {
    'station_station': {
        'code': 'station',
        'name': '工位名称',
        'product_line': '生产线'
    }
}

class ExcelToDb:
    def __init__(self, db_engine=None):
        self.engine = db_engine

    def excel_to_db(self, df, model):
        
        # 删除原表中所有数据
        model.objects.all().delete()

        # 获取模型的db_table名称
        db_table = model._meta.db_table
        # 从配置字典中获取字段名和列名的对应关系
        field_col_dict = table_field_col_dict[db_table]

        # 
        for row in df.itertuples():
            # 按照row遍历df，获取每一行的字段值
            cur_row = {}
            for key in field_col_dict:
                cur_row[key] = getattr(row, field_col_dict[key])

            model.objects.create(**cur_row)
            # model.objects.create(code=getattr(row, 'station'), name=getattr(row, '工位名称'), product_line=getattr(row, '生产线'))

    def read_excel(self, excel_path=excel_path):
        df = pd.read_excel(excel_path)
        return df


if 1:
    e2d = ExcelToDb(db_engine=db_engine)
    df = e2d.read_excel()
    # print(df)
    e2d.excel_to_db(df, Station)

    for row in df.itertuples():
        print(getattr(row, 'station'),getattr(row, '工位名称'),getattr(row, '生产线'))
        print('--------------')

'''
PRAGMA foreign_keys = 0;

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM station_station;

DROP TABLE station_station;

CREATE TABLE station_station (
    id      BIGINT PRIMARY KEY,
    station TEXT,
    工位名称    TEXT,
    生产线     TEXT
);

INSERT INTO station_station (
                                id,
                                station,
                                工位名称,
                                生产线
                            )
                            SELECT id,
                                   station,
                                   工位名称,
                                   生产线
                              FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

PRAGMA foreign_keys = 1;

'''